﻿/* Co so du lieu quan ly chung cu
Date: 06/06/2013

*/
--> Tao CSDL quanlychungcu
USE MASTER
GO

IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = 'quanlychungcu')
DROP DATABASE quanlychungcu
GO

CREATE DATABASE quanlychungcu
GO

--> - Thiet lap kieu ngay thang nam.
SET DATEFORMAT dmy
GO

--> - Dung ALXKHI, tao cac bang du lieu.
USE quanlychungcu
GO


-->1. TAI_KHOAN
CREATE TABLE TAI_KHOAN
(
	TEN_DANG_NHAP 		VARCHAR(20)	 NOT NULL,
	MAT_KHAU			VARCHAR(50) NOT NULL,
	QUYEN_TRUY_CAP		int,
	PRIMARY KEY (TEN_DANG_NHAP)
)
go

INSERT INTO TAI_KHOAN VALUES('admin', 'admin', 1)
INSERT INTO TAI_KHOAN VALUES('thi', 'thi', 2)
INSERT INTO TAI_KHOAN VALUES('phong', 'phong', 3)
INSERT INTO TAI_KHOAN VALUES('aa', 'aa', 4)
GO

--> 2.chung cu
CREATE TABLE CHUNG_CU
(
	Ma_CC				VARCHAR  (4)	NOT NULL,
	TEN_CC				NVARCHAR (30)	NULL,
	DIA_CHI				NVARCHAR(100),
	CONSTRAINT PK_CHUC_VU PRIMARY KEY (MA_CC)
)

GO
INSERT INTO CHUNG_CU VALUES('CC01', N'Chung cu phường 3', 'p3, tpvl')
INSERT INTO CHUNG_CU VALUES('CC02', N'Chung cu phường 4', 'p4, tpvl')

GO

CREATE TABLE BG_DICH_VU
(
	MA_BGDV 			VARCHAR(4)	 NOT NULL,
	TEN_DV				NVARCHAR(30),
	DON_GIA				DECIMAL,
	PRIMARY KEY (MA_BGDV)
	
)
GO
INSERT INTO BG_DICH_VU VALUES('DV01', N'Tiền Nhà', 3000000)
INSERT INTO BG_DICH_VU VALUES('DV02', N'Tiền Giữ Xe', 300000)
INSERT INTO BG_DICH_VU VALUES('DV03', N'Tiền Giữ Xe', 30000)

GO

--> 3.CAN HO
CREATE TABLE CAN_HO
(
	MA_CH				VARCHAR    (4)	NOT NULL,
	LO					NVARCHAR (30)	NULL,
	DIEN_TICH			NVARCHAR (30)	NULL,
	TRANG_THAI			NVARCHAR (15)	NULL,
	SO_LUONG_XE_HOI		INT,
	SO_LUONG_XE_MAY		INT,
	SO_LUONG_XE_DAP		INT,
	MA_CC				VARCHAR(4) CONSTRAINT FK_MA_CC	FOREIGN KEY REFERENCES CHUNG_CU(MA_CC),
	CONSTRAINT PK_CAN_HO PRIMARY KEY (MA_CH)
)
GO

INSERT INTO CAN_HO 
VALUES('CH01', N'Lô 1', '50', N'Đã Mướn', 1, 2, 3, 'CC01')

INSERT INTO CAN_HO 
VALUES('CH02', N'Lô 2', '50', N'Đã Mướn', 1, 2, 3, 'CC01')

INSERT INTO CAN_HO 
VALUES('CH03', N'Lô 3', '50', N'Đã Mướn', 1, 2, 3, 'CC01')

INSERT INTO CAN_HO 
VALUES('CH04', N'Lô 3', '50', N'Chưa Mướn', 1, 2, 3, 'CC01')

GO

CREATE TABLE CHI_TIET_DV
(
	MA_BGDV				VARCHAR(4) CONSTRAINT FK_KKHH		FOREIGN KEY REFERENCES BG_DICH_VU(MA_BGDV),
	MA_CH				VARCHAR(4) CONSTRAINT FK_CH_CS FOREIGN KEY REFERENCES CAN_HO(MA_CH),
	THANH_TIEN				DECIMAL,
	PRIMARY KEY (MA_BGDV, MA_CH)
	
)
GO

INSERT INTO CHI_TIET_DV VALUES('DV01', 'CH01', 3000000)
INSERT INTO CHI_TIET_DV VALUES('DV02', 'CH01', 300000)
INSERT INTO CHI_TIET_DV VALUES('DV03', 'CH01', 30000)

GO

CREATE TABLE BG_TIEN_NUOC
(
	MA_BGN 				VARCHAR(4)	 NOT NULL,
	TEN_BGN				NVARCHAR(30),
	DON_GIA				DECIMAL,
	NGAY_AP_DUNG		DATETIME,
	PRIMARY KEY (MA_BGN)
	
)
GO
INSERT INTO BG_TIEN_NUOC VALUES('DM01','0-10', 20000, '06/06/2013')
INSERT INTO BG_TIEN_NUOC VALUES('DM02','11-20', 30000, '06/06/2013')
INSERT INTO BG_TIEN_NUOC VALUES('DM03','21-30', 40000, '06/06/2013')
INSERT INTO BG_TIEN_NUOC VALUES('DM04','31- TrỞ Lên', 50000, '06/06/2013')

GO

CREATE TABLE KHACH_HANG
(
	MA_KH				VARCHAR (4)	NOT NULL,
	HOTEN_KH			NVARCHAR (50)	NULL,
	NG_SINH				DATETIME CHECK(NG_SINH < GETDATE()),
	D_CHI				NVARCHAR (100)	NULL,
	SDT					VARCHAR (13)	NULL,
	CONSTRAINT PK_NHAN_VIEN PRIMARY KEY (MA_KH)
)

GO

INSERT INTO KHACH_HANG VALUES('KH01', N'Đào Minh Quốc' , '01/01/1990', N'Đồng Tháp', '0932885555' )
INSERT INTO KHACH_HANG VALUES('KH02', N'Nguyễn Công Trường' , '01/01/1990', N'Vũng Liêm', '0932933320' )

GO

CREATE TABLE KH_CH
(
	MA_KH				VARCHAR(4) CONSTRAINT FK_KH	FOREIGN KEY REFERENCES KHACH_HANG(MA_KH),
	MA_CH				VARCHAR(4) CONSTRAINT FK_FFF	FOREIGN KEY REFERENCES CAN_HO(MA_CH),
	NGAY_THUE			DATETIME,
	NGAY_TRA			DATETIME,
	CONSTRAINT PK_NVCC PRIMARY KEY (MA_KH, MA_CH)
)

GO

INSERT INTO KH_CH VALUES('KH01', 'CH01', '01/06/2013', '01/07/2013')

INSERT INTO KH_CH VALUES('KH02', 'CH02', '01/06/2013', '01/07/2013')

GO

CREATE TABLE BG_CS_NUOC
(
	MA_CS				VARCHAR(4)	 NOT NULL,
	THANG_NAM			DATETIME,
	CHI_SO				FLOAT,
	MA_BGN				VARCHAR(4) CONSTRAINT FK_GIA FOREIGN KEY REFERENCES BG_TIEN_NUOC(MA_BGN),
	MA_CH				VARCHAR(4) CONSTRAINT FK_CH_CSS FOREIGN KEY REFERENCES CAN_HO(MA_CH),
	PRIMARY KEY (MA_CS, MA_CH)
	
)
go
INSERT INTO BG_CS_NUOC VALUES('CS01', '06/06/2013', 10, 'DM01', 'CH01')
INSERT INTO BG_CS_NUOC VALUES('CS02', '06/06/2013', 10, 'DM01', 'CH02')

go



--> 4.NHAN VIEN
CREATE TABLE NHAN_VIEN
(
	MA_NV				VARCHAR (4)	NOT NULL,
	HOTEN_NV			NVARCHAR (50)	NULL,
	TEN_DANG_NHAP		VARCHAR(20) CONSTRAINT FK_NHAN_VIEN_TAI_KHOAN FOREIGN KEY REFERENCES TAI_KHOAN(TEN_DANG_NHAP),
	NGAY_SINH			DATETIME CHECK(NGAY_SINH < GETDATE()),
	DIA_CHI				NVARCHAR (100)	NULL,
	SO_DIEN_THOAI		VARCHAR (13)	NULL,
	HKTT				NVARCHAR(100),
	CHUC_VU				NVARCHAR(30),
	LUONG				DECIMAL			DEFAULT 0,
	CONSTRAINT PK_N_VIEN PRIMARY KEY (MA_NV)
)

GO

INSERT INTO NHAN_VIEN 
VALUES('NV01', N'Nguyễn Minh Trí', 'admin', '01/01/1990', N'Phú Quế', '01687446888', N'Phú Quế', N'Quản lÝ', 5000000 )

INSERT INTO NHAN_VIEN 
VALUES('NV02', N'Huỳnh Văn Thi', 'THI', '01/01/1990', N'Phú Quế', '01687446888', N'Phú Quế', N'Quản lÝ', 5000000 )
INSERT INTO NHAN_VIEN 
VALUES('NV03', N'Nguyễn Tấn Đạt', 'phong', '01/01/1990', N'Phú Quế', '01687446888', N'Phú Quế', N'Quản lÝ', 5000000 )

INSERT INTO NHAN_VIEN 
VALUES('NV04', N'Nguyễn Hồng Vân', 'aa', '01/01/1990', N'Phú Quế', '01687446888', N'Phú Quế', N'Quản lÝ', 5000000 )

GO

--> 5. NV_CC
CREATE TABLE NV_CC
(
	MA_NV				VARCHAR(4) CONSTRAINT FK_CHCU	FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	MA_CC				VARCHAR(4) CONSTRAINT FK_NHV	FOREIGN KEY REFERENCES CHUNG_CU(MA_CC),
	NGAY_BD				DATETIME,
	NGAY_KT				DATETIME,
	CONSTRAINT PK_NVCKKC PRIMARY KEY (MA_NV, MA_CC)
)

GO

INSERT INTO NV_CC VALUES('NV01', 'CC01', '01/06/2013', '01/07/2013')

INSERT INTO NV_CC VALUES('NV02', 'CC02', '01/06/2013', '01/08/2013')

GO
--> 6.LOAI HOA_DON
CREATE TABLE LOAI_HD
(
	MA_LHD				VARCHAR(4)	NOT NULL,
	TEN_LHD				NVARCHAR(30)	NULL,
	CONSTRAINT PK_LHD PRIMARY KEY (MA_LHD)
)

GO

INSERT INTO LOAI_HD VALUES('AA01', N'Hóa Đơn Tổng Quát')
INSERT INTO LOAI_HD VALUES('AA02', N'Hóa Đơn Phụ Thu')

GO

--> . HOA DON
CREATE TABLE HOA_DON
(
	STT_HD				VARCHAR(4)  PRIMARY KEY  NOT NULL,
	NGAY_LAP			DATETIME ,	
	MA_NV				VARCHAR(4)		NOT NULL
	CONSTRAINT FK_PHIEU_DAT_HANG_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	MA_LHD				VARCHAR(4)		NOT NULL
	CONSTRAINT FK_LHD						FOREIGN KEY REFERENCES LOAI_HD(MA_LHD),
	MA_CH				VARCHAR(4)		NOT NULL
	CONSTRAINT FK_PHIEU_DAT_HANG_KHACH_HANG FOREIGN KEY REFERENCES CAN_HO(MA_CH),
	TONG_TIEN			DECIMAL		    NOT NULL,

)
GO

INSERT INTO HOA_DON VALUES('HD01', '10/06/2013', 'NV01', 'AA01', 'CH01', 3520000)
INSERT INTO HOA_DON VALUES('HD02', '10/06/2013', 'NV02', 'AA02', 'CH02', 3520000)







